package test.xg2013.totoro.test.service;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.List;

import org.junit.Test;

import test.xg2013.totoro.test.dataset.util.XlsDataSetBeanFactory;
import test.xg2013.totoro.test.domain.GraduateProjectFromType;
import test.xg2013.totoro.test.domain.ReplyGroupFromExcel;
import test.xg2013.totoro.test.domain.Role2Resource;
import test.xg2013.totoro.test.domain.RoleRes2Resource;
import test.xg2013.totoro.test.domain.User2Role;
import xg2013.totoro.domain.ClassRoom;
import xg2013.totoro.domain.Department;
import xg2013.totoro.domain.DesignProject;
import xg2013.totoro.domain.Employee;
import xg2013.totoro.domain.Major;
import xg2013.totoro.domain.PaperProject;
import xg2013.totoro.domain.ProTitle;
import xg2013.totoro.domain.ProjectFrom;
import xg2013.totoro.domain.ProjectType;
import xg2013.totoro.domain.ReplyGroup;
import xg2013.totoro.domain.ReplyTime;
import xg2013.totoro.domain.Resource;
import xg2013.totoro.domain.Role;
import xg2013.totoro.domain.RoleResource;
import xg2013.totoro.domain.School;
import xg2013.totoro.domain.Student;
import xg2013.totoro.domain.StudentClass;
import xg2013.totoro.domain.Tutor;
import xg2013.totoro.domain.User;
import xg2013.totoro.domain.UserRole;

public class ExcelToDatabase extends BaseData {

@Test
public void test() throws Exception{
	this.saveEntity();
	//this.saveUserRole();

}

public void saveEntity() throws Exception{
	this.saveDepartment();
	this.saveMajor();
	this.saveProTitle();
	this.saveResource();
	this.saveRole();

	this.saveSchool();
	this.saveStudentClass();
	
	this.saveUser();
	this.saveEmployee();
	this.saveClassRoom();
	this.saveDesignProject();
	this.savePaperProject();
	this.saveProjectFrom();
	this.saveProjectType();
	this.saveStudent();
	this.saveUserRole();
	this.saveRoleResource();
	this.saveDesignProjectFromType();
	this.savePaperProjectFromType();
	this.saveReplyGroup();
	this.saveResourceChild();

}

public void saveUser() throws Exception{
	List<User> users = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "user", User.class);
	for(User user:users){
		userService.save(user);
	}
}

public void saveRole() throws Exception{
	List<Role> roles = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "role", Role.class);
	for(Role role:roles){
		//System.out.println(role.getDescription());
		roleService.save(role);
	}
}
public void saveResource() throws Exception{
	List<Resource> resources = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "resource", Resource.class);
	for(Resource resource:resources){
		resourceService.save(resource);
	}
}
public void saveResourceChild() throws Exception{
	List<RoleRes2Resource> roleRes2Resources = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "resc_childs", RoleRes2Resource.class);
	Collection<Resource> childs = null;
	Resource resource = null;
	Resource childResource = null;
	for(int i = 1;i<=resourceService.countAll();i++){
		resource = resourceService.get(i);
		childs = new HashSet<Resource>();
		for(RoleRes2Resource res2Resource:roleRes2Resources){
			if (res2Resource.getResourceId().equals(i)) {
				childResource = resourceService.get(res2Resource.getChildId());
				childs.add(childResource);
			}
		}
		if(childs != null){
			resource.setChild(childs);
		}
	}
}

public void saveSchool() throws Exception{
	List<School> schools = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "school", School.class);
	for(School school:schools){
		schoolService.save(school);
	}
}
public void saveDepartment() throws Exception{
	List<Department> departments = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "department", Department.class);
	for(Department department:departments){
		departmentService.save(department);
	}
}
public void saveMajor() throws Exception{
	List<Major> majors = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "major", Major.class);
	for(Major major:majors){
		majorService.save(major);
	}
}
public void saveStudentClass() throws Exception{
	List<StudentClass> studentClasses = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "student_class", StudentClass.class);
	for(StudentClass studentClass:studentClasses){
		studentClassService.save(studentClass);
	}
}
public void saveProTitle() throws Exception{
	List<ProTitle> proTitles = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "protitle", ProTitle.class);
	for(ProTitle proTitle:proTitles){
		proTitleService.save(proTitle);
	}
}
public void saveEmployee() throws Exception{
	List<Employee> employees = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "employee", Employee.class);
	User user = null;
	for(Employee employee:employees){
		user = userService.uniqueResult("username", employee.getNo());
		employee.setUser(user);
		employeeService.save(employee);
	}
}

public void saveClassRoom() throws Exception{
	List<ClassRoom> classRooms = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "class_room", ClassRoom.class);
	for(ClassRoom classRoom:classRooms){
		classRoomService.save(classRoom);
	}
}
public void saveDesignProject() throws Exception{
	List<DesignProject> designProjects = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "design_project", DesignProject.class);
	
	for(DesignProject designProject:designProjects){
		designProject.setYear(2013);

		designProjectService.save(designProject);
	}

}
public void savePaperProject() throws Exception{
	List<PaperProject> paperProjects = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "paper_project", PaperProject.class);
	
	for(PaperProject paperProject:paperProjects){
		//paperProject.setYear(2013);
		paperProjectService.save(paperProject);
	}

}
public void saveProjectFrom() throws Exception{
	List<ProjectFrom> projectFroms = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "project_from", ProjectFrom.class);
	
	for(ProjectFrom projectFrom:projectFroms){
		projectFromService.save(projectFrom);
	}

}

public void saveProjectType() throws Exception{
	List<ProjectType> projectTypes = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "project_type", ProjectType.class);
	
	for(ProjectType projectType:projectTypes){
		projectTypeService.save(projectType);
	}

}
public void saveStudent() throws Exception{
	List<Student> students = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "student", Student.class);
	
	for(Student student:students){
		studentService.save(student);
	}

}
public void saveUserRole() throws Exception{
	List<User2Role> user2Roles = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "user_role", User2Role.class);	
	Collection<User> users = userService.list();
	Collection<UserRole> userRoles = null;
	UserRole userRole = null;
	Role role = null;
	for(User user:users){
		userRoles = new HashSet<UserRole>();
		for(User2Role user2Role:user2Roles){
			//System.out.println(user.getId()+" "+(user2Role.getRole()));
			if (user.getId().equals(user2Role.getUser())) {

				role = roleService.get(user2Role.getRole());
				userRole = new UserRole();
				userRole.setUser(user);
				userRole.setRole(role);
				userRoles.add(userRole);
				
				userRoleService.save(userRole);
			}
			user.setUserRoles(userRoles);
			
		}
		
	}

}
public void saveRoleResource() throws Exception{
	Collection<Role2Resource> role2Resources = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "role_resc", Role2Resource.class);	
	Collection<Role> roles = roleService.list();
	Collection<RoleResource> roleResources = null;
	RoleResource roleResource = null;
	Resource resource = null;
	for(Role role:roles){
		roleResources = new HashSet<RoleResource>();
		for(Role2Resource role2Resource:role2Resources){
			//System.out.println(role.getId().equals(role2Resource.getRole()));

			if(role.getId().equals(role2Resource.getRole())){
				resource = resourceService.get(role2Resource.getResource());
				roleResource = new RoleResource();
				roleResource.setRole(role);
				roleResource.setResource(resource);
				roleResources.add(roleResource);
				roleResourceService.save(roleResource);
			}
		}
		role.setRoleResources(roleResources);
		
	}
}
public void saveDesignProjectFromType() throws Exception{
	Collection<GraduateProjectFromType> graduateProjectFromTypes = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "designProjectFromType", GraduateProjectFromType.class);	
	DesignProject designProject = null;
	ProjectFrom projectFrom =null;
	ProjectType projectType = null;
	Tutor proposer = null;
	int poor = 0;
	Collection<Tutor> tutors = tutorService.list();
	int minTutorId = -1;
	int minTutorIdInExcel = -1;
	for(Tutor tu:tutors){
		if(minTutorId < 0){
			minTutorId = tu.getId();
		} else {
			minTutorId = minTutorId < tu.getId() ? minTutorId : tu.getId();
			//System.out.println(minTutorId);
			break;
		}
	}
	
	for(GraduateProjectFromType tu:graduateProjectFromTypes){
		if(minTutorIdInExcel < 0){
			minTutorIdInExcel = tu.getProposerId();
		} else {
			minTutorIdInExcel = minTutorIdInExcel < tu.getProposerId() ? minTutorIdInExcel : tu.getProposerId();
			//System.out.println(minTutorIdInExcel);
			break;
		}
	}
	poor = minTutorId - minTutorIdInExcel;
	
	for(GraduateProjectFromType graduateProjectFromType:graduateProjectFromTypes){
		    proposer = employeeService.get(graduateProjectFromType.getProposerId()+poor);
			designProject = designProjectService.get(graduateProjectFromType.getProjectId());
			projectFrom = projectFromService.get(graduateProjectFromType.getProjectFromId());
			projectType = projectTypeService.get(graduateProjectFromType.getProjectTypeId());
			designProject.setProjectFrom(projectFrom);
			designProject.setProjectType(projectType);
			designProject.setProposer(proposer);
	}
}
public void savePaperProjectFromType() throws Exception{
	Collection<GraduateProjectFromType> graduateProjectFromTypes = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "paperProjectFromType", GraduateProjectFromType.class);	
	PaperProject paperProject = null;
	ProjectFrom projectFrom =null;
	ProjectType projectType = null;
	Tutor proposer = null;
	int poor = 0;
	Collection<Tutor> tutors = tutorService.list();
	int minTutorId = -1;
	int minTutorIdInExcel = -1;
	for(Tutor tu:tutors){
		if(minTutorId < 0){
			minTutorId = tu.getId();
		} else {
			minTutorId = minTutorId < tu.getId() ? minTutorId : tu.getId();
			//System.out.println(minTutorId);
			break;
		}
	}
	
	for(GraduateProjectFromType tu:graduateProjectFromTypes){
		if(minTutorIdInExcel < 0){
			minTutorIdInExcel = tu.getProposerId();
		} else {
			minTutorIdInExcel = minTutorIdInExcel < tu.getProposerId() ? minTutorIdInExcel : tu.getProposerId();
			//System.out.println(minTutorIdInExcel);
			break;
		}
	}
	poor = minTutorId - minTutorIdInExcel;
	for(GraduateProjectFromType graduateProjectFromType:graduateProjectFromTypes){
		 	proposer = tutorService.get(graduateProjectFromType.getProposerId()+poor);
			paperProject = paperProjectService.get(graduateProjectFromType.getProjectId());
			projectFrom = projectFromService.get(graduateProjectFromType.getProjectFromId());
			projectType = projectTypeService.get(graduateProjectFromType.getProjectTypeId());
			paperProject.setProjectFrom(projectFrom);
			paperProject.setProjectType(projectType);
			paperProject.setProposer(proposer);
	}
}
public void saveReplyGroup() throws Exception{
	List<ReplyGroupFromExcel> replyGroups = XlsDataSetBeanFactory.createBeans(ExcelToDatabase.class, "data/data.xls", "reply_group", ReplyGroupFromExcel.class);
	ReplyGroup replyGroup = null;
	ReplyTime replyTime = null;
	Calendar calendar = Calendar.getInstance();
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");;
	Date date = null;
	Department department = null;
	Tutor leader = null;
	ClassRoom classRoom = null;
	for(ReplyGroupFromExcel group:replyGroups){
		replyGroup = new ReplyGroup();
		classRoom = classRoomService.get(group.getDepartmentId());
		department = departmentService.get(group.getDepartmentId());
		//System.out.println(department.getDescription());
		replyGroup.setDescription(group.getDescription());
		replyTime = new ReplyTime(); 
		date = sdf.parse(group.getBeginTime());
		//System.out.println(group.getBeginTime());
		calendar.setTime(date);
		//System.out.println(calendar.getTime());
		replyTime.setBeginTime(calendar);
		date = sdf.parse(group.getOverTime());
		calendar.setTime(date);
		//System.out.println(calendar.getTime());
		replyTime.setOverTime(calendar);
		leader = tutorService.uniqueResult("no", group.getLeaderNo());
		replyGroup.setReplyTime(replyTime);
		replyGroup.setLeader(leader);
		replyGroup.setDepartment(department);
		replyGroup.setClassRoom(classRoom);
		replyGroupService.save(replyGroup);
		//System.out.println(replyGroupService.get(60).getReplyTime().getBeginTime().getTime());
	}

}

}
